More on Alter

More on Alter#

It is hard to predict what indexes to create without observing the access patterns for an application. We can add, remove, or modify indexes after the application is deployed. Note that modifying indexes doesn’t change the data in the table.

Example Syntax#

ALTER TABLE oldTableName

RENAME newTableName;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/19lesson.sh and wait for the MySQL prompt to start-up.

1. We can also rename a table after creating it. In the snippet below, we rename the Actors table to ActorsTable as follows:

   ALTER TABLE Actors RENAME ActorsTable;
   

2. We have already discussed dropping tables, but we can use a slightly improved query using the IF EXISTS clause.

   DROP TABLE IF EXISTS ActorsTable;
   

   Executing the above query multiple times will not result in an error once the table is already deleted. The IF EXISTS clause is useful when deletion occurs in an automated script. The script continues to execute even if the table has previously been deleted.

3. We can delete multiple tables in a single statement by specifying a comma-separated list of table names to delete.

   DROP TABLE IF EXISTS Table1, Table2, Table3;
   

4. We can drop the entire database as follows:

   DROP DATABASE IF EXISTS MovieIndustry;